package org.jerrymouse.jsa4j.db.kv.derby;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jerrymouse.jsa4j.db.kv.DB;
import org.jerrymouse.jsa4j.db.kv.KeyMaker;

public class DerbyDB implements DB {
	private DBUtil dbUtil;
	private Log log = LogFactory.getLog(DerbyDB.class);

	public DerbyDB(DBUtil dbUtil) {
		this.dbUtil = dbUtil;
	}

	/**
	 * delete from KeyValue where id=?
	 * 
	 * @throws SQLException
	 */
	private void delete(String key, Connection connection) throws SQLException {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection
					.prepareStatement("delete from KeyValue where id=?");
			preparedStatement.setString(1, key);
			preparedStatement.executeUpdate();
		} finally {
			preparedStatement.close();
		}
	}

	private void insert(String key, String value, Connection connection)
			throws SQLException {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection
					.prepareStatement("insert into KeyValue (value, id) values (?, ?)");
			preparedStatement.setString(1, value);
			preparedStatement.setString(2, key);
			preparedStatement.executeUpdate();
		} finally {
			preparedStatement.close();
		}
	}

	// private String insert(String value, Connection connection)
	// throws SQLException {
	// String key = KeyMaker.make();
	// insert(key, value, connection);
	// return key;
	// }

	private void update(String key, String value, Connection connection)
			throws SQLException {
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection
					.prepareStatement("update KeyValue set value=? where id=?");
			preparedStatement.setString(1, value);
			preparedStatement.setString(2, key);
			preparedStatement.executeUpdate();
		} finally {
			preparedStatement.close();
		}
	}

	/**
	 * select keyvalue0_.id as id2_0_, keyvalue0_.value as value2_0_ from
	 * KeyValue keyvalue0_ where keyvalue0_.id=?
	 */
	@Override
	public String get(String key) {
		String value = null;
		Connection connection = null;
		try {
			try {
				connection = dbUtil.getConnection();
				value = get(key, connection);
				connection.commit();
			} finally {
				connection.close();
			}
		} catch (SQLException e) {
			error(e);
		}
		return value;
	}

	private void error(SQLException e) {
		if (e.getMessage().contains("Table/View 'KEYVALUE' does not exist.")) {
			dbUtil.createTable();
		} else
			e.printStackTrace();
	}

	public String get(String key, Connection connection) throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			preparedStatement = connection
					.prepareStatement("select keyvalue0_.id as id2_0_, keyvalue0_.value as value2_0_ from KeyValue keyvalue0_ where keyvalue0_.id=?");
			preparedStatement.setString(1, key);
			rs = preparedStatement.executeQuery();
			if (!rs.next()) {
				return null;
			}
			return rs.getString(2);
		} finally {
			if (rs != null)
				rs.close();
			if (preparedStatement != null)
				preparedStatement.close();
		}

	}

	/**
	 * insert into KeyValue (value, id) values (?, ?)<br/>
	 * update KeyValue set value=? where id=?
	 */
	@Override
	public String put(String key, String value) {
		if (key == null)
			return null;
		String valueOrg = null;
		Connection connection = null;
		try {
			try {
				connection = dbUtil.getConnection();
				valueOrg = get(key);
				if (valueOrg != null) {
					if (value == null) {
						delete(key, connection);
					} else {
						update(key, value, connection);
					}
				} else if (value != null) {
					insert(key, value, connection);
				} else {
					return null;
				}
			} finally {
				if (connection != null) {
					connection.commit();
					connection.close();
				}
			}
		} catch (SQLException e) {
			error(e);
		}
		return key;

	}

}
